package com.inforich.app.utils;

import cn.hutool.core.date.DateUtil;
import com.inforich.app.api.dto.BookingDTO;
import com.inforich.common.utils.DateUtils;
import com.inforich.projectManage.domain.Project;
import com.inforich.projectManage.domain.ProjectRubbishExpand;
import com.inforich.projectManage.domain.ProjectRubbishRemoval;
import com.inforich.projectManage.domain.ProjectRubbishReport;
import com.inforich.projectManage.domain.StageRubbishDetail;
import java.math.BigDecimal;
import java.util.Calendar;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

public class ExportExcelHelper {

  private static final float TITLE_HEIGHT = 40f;
  private static final float ROW_HEIGHT = 30f;

  /**
   * 新、改、扩建工程建筑垃圾资源化利用信息登记表
   *
   * @param project                  Project
   * @param rubbishExpand            ProjectRubbishExpand
   * @param idField                  Map<String,String>
   * @param stageRubbishDetailsGroup List<StageRubbishDetail>>
   * @return SXSSFWorkbook
   */
  public static SXSSFWorkbook createRegisterTable(Project project,
      ProjectRubbishExpand rubbishExpand,
      Map<String, String> idField, Map<String, List<StageRubbishDetail>> stageRubbishDetailsGroup) {

    SXSSFWorkbook wb = new SXSSFWorkbook();
    Sheet sheet = wb.createSheet("新、改、扩建工程建筑垃圾资源化利用信息登记表");
    sheet.setColumnWidth(1, 5000);
    sheet.setColumnWidth(2, 4000);
    sheet.setColumnWidth(3, 4000);
    sheet.setColumnWidth(4, 4000);
    sheet.setColumnWidth(5, 5000);
    sheet.setColumnWidth(6, 4500);
    sheet.setColumnWidth(7, 4000);
    sheet.setColumnWidth(8, 4000);

    CellStyle titleCellStyle = wb.createCellStyle();
    Font titleCellFont = wb.createFont();
    titleCellFont.setBold(true);
    titleCellFont.setFontName("微软雅黑");
    titleCellFont.setFontHeightInPoints((short) 14);
    titleCellStyle.setFont(titleCellFont);
    titleCellStyle.setAlignment(HorizontalAlignment.CENTER);
    titleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

    CellStyle labelCellStyle = wb.createCellStyle();
    Font labelCellFont = wb.createFont();
    labelCellFont.setBold(true);
    labelCellFont.setFontName("微软雅黑");
    labelCellFont.setFontHeightInPoints((short) 11);
    labelCellStyle.setFont(labelCellFont);
    labelCellStyle.setBorderBottom(BorderStyle.THIN);
    labelCellStyle.setBorderLeft(BorderStyle.THIN);
    labelCellStyle.setBorderTop(BorderStyle.THIN);
    labelCellStyle.setBorderRight(BorderStyle.THIN);
    labelCellStyle.setWrapText(true);
    labelCellStyle.setAlignment(HorizontalAlignment.CENTER);
    labelCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

    CellStyle cellStyle = wb.createCellStyle();
    Font cellFont = wb.createFont();
    cellFont.setFontName("微软雅黑");
    cellFont.setFontHeightInPoints((short) 11);
    cellStyle.setFont(cellFont);
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setWrapText(true);
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

    Row row = sheet.createRow(0);
    row.setHeightInPoints(40f);
    Cell cell = row.createCell(0);
    cell.setCellStyle(titleCellStyle);
    cell.setCellValue("新、改、扩工程建筑垃圾资源化利用信息登记表");

    for (int i = 1; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));

    row = sheet.createRow(1);
    row.setHeightInPoints(30f);
    cell = row.createCell(0);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("项目基本信息");

    cell = row.createCell(1);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("项目名称");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(project.getName());
    for (int i = 3; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }

    sheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 8));

    row = sheet.createRow(2);
    row.setHeightInPoints(30f);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("项目地址");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(project.getAddress());
    for (int i = 3; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 2, 8));

    row = sheet.createRow(3);
    row.setHeightInPoints(50f);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("建设内容");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(rubbishExpand.getBuildingContent());
    for (int i = 3; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(3, 3, 2, 8));

    row = sheet.createRow(4);
    row.setHeightInPoints(30f);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("建设周期");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(rubbishExpand.getBuildingPeriod());
    for (int i = 3; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(4, 4, 2, 8));

    row = sheet.createRow(5);
    row.setHeightInPoints(30f);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("工程物类别");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(idField.get("buildingType"));
    for (int i = 3; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(5, 5, 2, 8));

    row = sheet.createRow(6);
    row.setHeightInPoints(30f);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("结构类型");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(idField.get("structureType"));
    for (int i = 3; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(6, 6, 2, 8));

    row = sheet.createRow(7);
    row.setHeightInPoints(30f);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("结构层次");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(rubbishExpand.getStructureLevel());
    for (int i = 3; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(7, 7, 2, 8));

    row = sheet.createRow(8);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("建设单位");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("单位名称");

    cell = row.createCell(3);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(rubbishExpand.getBuildingDept());

    for (int i = 4; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(8, 8, 3, 8));

    row = sheet.createRow(9);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("联系人及电话");

    cell = row.createCell(3);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(rubbishExpand.getBuildingPerson() + "-" + rubbishExpand.getBuildingPhone());

    for (int i = 4; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(9, 9, 3, 8));

    sheet.addMergedRegion(new CellRangeAddress(8, 9, 1, 1));

    row = sheet.createRow(10);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("施工单位");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("单位名称");

    cell = row.createCell(3);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(rubbishExpand.getConstructionDept());

    for (int i = 4; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(10, 10, 3, 8));

    row = sheet.createRow(11);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("联系人及电话");

    cell = row.createCell(3);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(
        rubbishExpand.getConstructionPerson() + "-" + rubbishExpand.getConstructionPhone());

    for (int i = 4; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(11, 11, 3, 8));

    sheet.addMergedRegion(new CellRangeAddress(10, 11, 1, 1));

    row = sheet.createRow(12);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("监理单位");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("单位名称");

    cell = row.createCell(3);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(rubbishExpand.getSupervisorDept());

    for (int i = 4; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(12, 12, 3, 8));

    row = sheet.createRow(13);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("总监及电话");

    cell = row.createCell(3);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(
        rubbishExpand.getSupervisorPerson() + "-" + rubbishExpand.getSupervisorPhone());

    for (int i = 4; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(13, 13, 3, 8));

    sheet.addMergedRegion(new CellRangeAddress(12, 13, 1, 1));

    row = sheet.createRow(14);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("资源化利用处置单位");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("单位名称");

    cell = row.createCell(3);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(idField.get("disposalLand"));

    for (int i = 4; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(14, 14, 3, 8));

    row = sheet.createRow(15);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("联系人及电话");

    cell = row.createCell(3);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(
        rubbishExpand.getDisposalLandPerson() + "-" + rubbishExpand.getDisposalLandPhone());

    for (int i = 4; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(15, 15, 3, 8));

    sheet.addMergedRegion(new CellRangeAddress(14, 15, 1, 1));

    row = sheet.createRow(16);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("运输单位");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("单位名称");

    cell = row.createCell(3);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(idField.get("transportCompany"));

    for (int i = 4; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(16, 16, 3, 8));

    row = sheet.createRow(17);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("联系人及电话");

    cell = row.createCell(3);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(
        rubbishExpand.getTransportCompanyPerson() + "-" + rubbishExpand.getTransportCompanyPhone());

    for (int i = 4; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(17, 17, 3, 8));

    row = sheet.createRow(18);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("车辆信息");

    cell = row.createCell(3);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(idField.get("transportCompanyCarInfo"));

    for (int i = 4; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(18, 18, 3, 8));

    sheet.addMergedRegion(new CellRangeAddress(16, 18, 1, 1));

    row = sheet.createRow(19);
    row.setHeightInPoints(40f);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("预计垃圾产生量");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("预计产生建筑垃圾约" + convertBigDecimal(rubbishExpand.getRubbishPlanArea()) + "m²,约"
        + convertBigDecimal(rubbishExpand.getRubbishPlanWeight()) +
        "吨,其中可利用的约" + convertBigDecimal(rubbishExpand.getRubbishRecycleWeight()) + "吨,预计利用率可以达到"
        + convertBigDecimal(rubbishExpand.getRubbishRecycleRatio()) + "%");
    for (int i = 3; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(19, 19, 2, 8));

    row = sheet.createRow(20);
    row.setHeightInPoints(40f);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("预计垃圾清运时间");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(convertDate(rubbishExpand.getBaseStageStartDate()) + "-" + convertDate(
        rubbishExpand.getBaseStageEndDate()));
    for (int i = 3; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(20, 20, 2, 8));

    sheet.addMergedRegion(new CellRangeAddress(1, 20, 0, 0));

    row = sheet.createRow(21);
    row.setHeightInPoints(30f);
    cell = row.createCell(0);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("资源化利用情况");

    cell = row.createCell(1);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("项目进度");

    cell = row.createCell(2);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("清运时间");

    cell = row.createCell(3);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("垃圾种类");

    cell = row.createCell(4);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(5);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("资源化利用再生材料");

    cell = row.createCell(6);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("去向及地址");

    cell = row.createCell(7);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("数量（t）");

    cell = row.createCell(8);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("占比（%）");

    sheet.addMergedRegion(new CellRangeAddress(21, 21, 3, 4));

    int rowIndex = 1;
    int stageRowStartIndex = 21 + rowIndex;

    List<StageRubbishDetail> baseList = stageRubbishDetailsGroup.get("base");
    if (baseList != null && baseList.size() > 0) {
      Map<String, List<StageRubbishDetail>> baseGroup = getRubbishDetailGroup(baseList);
      for (Map.Entry<String, List<StageRubbishDetail>> entry : baseGroup.entrySet()) {
        List<StageRubbishDetail> groupList = entry.getValue();
        for (StageRubbishDetail stageRubbishDetail : groupList) {
          row = sheet.createRow(21 + rowIndex);
          row.setHeightInPoints(30f);
          cell = row.createCell(0);
          cell.setCellStyle(cellStyle);
          cell.setCellValue("");

          cell = row.createCell(1);
          cell.setCellStyle(labelCellStyle);
          cell.setCellValue("基础阶段");

          cell = row.createCell(2);
          cell.setCellStyle(cellStyle);
          cell.setCellValue(convertDate(rubbishExpand.getBaseStageStartDate()) + "-" + convertDate(
              rubbishExpand.getBaseStageEndDate()));

          cell = row.createCell(3);
          cell.setCellStyle(labelCellStyle);
          cell.setCellValue(stageRubbishDetail.getRubbishType());

          cell = row.createCell(4);
          cell.setCellStyle(labelCellStyle);
          cell.setCellValue(stageRubbishDetail.getRubbishName());

          cell = row.createCell(5);
          cell.setCellStyle(cellStyle);
          cell.setCellValue(stageRubbishDetail.getResource());

          cell = row.createCell(6);
          cell.setCellStyle(cellStyle);
          cell.setCellValue(stageRubbishDetail.getAddress());

          cell = row.createCell(7);
          cell.setCellStyle(cellStyle);
          cell.setCellValue(stageRubbishDetail.getNum());

          cell = row.createCell(8);
          cell.setCellStyle(cellStyle);
          cell.setCellValue(stageRubbishDetail.getProportion());

          rowIndex = rowIndex + 1;
        }
      }

      if (baseList.size() > 1) {
        sheet.addMergedRegion(
            new CellRangeAddress(stageRowStartIndex, stageRowStartIndex + baseList.size() - 1, 1,
                1));
        sheet.addMergedRegion(
            new CellRangeAddress(stageRowStartIndex, stageRowStartIndex + baseList.size() - 1, 2,
                2));
        int startRowIndex = stageRowStartIndex;
        for (Map.Entry<String, List<StageRubbishDetail>> entry : baseGroup.entrySet()) {
          List<StageRubbishDetail> stageRubbishDetailList = entry.getValue();
          if (stageRubbishDetailList.size() > 1) {
            sheet.addMergedRegion(
                new CellRangeAddress(startRowIndex,
                    startRowIndex + stageRubbishDetailList.size() - 1, 3,
                    3));
          }
          startRowIndex = startRowIndex + stageRubbishDetailList.size();
        }
      }
    }

    rowIndex = 1;
    stageRowStartIndex = 21 + baseList.size() + rowIndex;
    List<StageRubbishDetail> mainList = stageRubbishDetailsGroup.get("main");
    if (mainList != null && mainList.size() > 0) {
      Map<String, List<StageRubbishDetail>> mainGroup = getRubbishDetailGroup(mainList);
      for (Map.Entry<String, List<StageRubbishDetail>> entry : mainGroup.entrySet()) {
        List<StageRubbishDetail> groupList = entry.getValue();
        for (StageRubbishDetail stageRubbishDetail : groupList) {
          row = sheet.createRow(21 + baseList.size() + rowIndex);
          row.setHeightInPoints(30f);
          cell = row.createCell(0);
          cell.setCellStyle(cellStyle);
          cell.setCellValue("");

          cell = row.createCell(1);
          cell.setCellStyle(labelCellStyle);
          cell.setCellValue("主体阶段");

          cell = row.createCell(2);
          cell.setCellStyle(cellStyle);
          cell.setCellValue(convertDate(rubbishExpand.getMainStageStartDate()) + "-" + convertDate(
              rubbishExpand.getMainStageEndDate()));

          cell = row.createCell(3);
          cell.setCellStyle(labelCellStyle);
          cell.setCellValue(stageRubbishDetail.getRubbishType());

          cell = row.createCell(4);
          cell.setCellStyle(labelCellStyle);
          cell.setCellValue(stageRubbishDetail.getRubbishName());

          cell = row.createCell(5);
          cell.setCellStyle(cellStyle);
          cell.setCellValue(stageRubbishDetail.getResource());

          cell = row.createCell(6);
          cell.setCellStyle(cellStyle);
          cell.setCellValue(stageRubbishDetail.getAddress());

          cell = row.createCell(7);
          cell.setCellStyle(cellStyle);
          cell.setCellValue(stageRubbishDetail.getNum());

          cell = row.createCell(8);
          cell.setCellStyle(cellStyle);
          cell.setCellValue(stageRubbishDetail.getProportion());

          rowIndex = rowIndex + 1;
        }
      }

      if (mainList.size() > 1) {
        sheet.addMergedRegion(
            new CellRangeAddress(stageRowStartIndex, stageRowStartIndex + mainList.size() - 1, 1,
                1));
        sheet.addMergedRegion(
            new CellRangeAddress(stageRowStartIndex, stageRowStartIndex + mainList.size() - 1, 2,
                2));
        int startRowIndex = stageRowStartIndex;
        for (Map.Entry<String, List<StageRubbishDetail>> entry : mainGroup.entrySet()) {
          List<StageRubbishDetail> stageRubbishDetailList = entry.getValue();
          if (stageRubbishDetailList.size() > 1) {
            sheet.addMergedRegion(
                new CellRangeAddress(startRowIndex,
                    startRowIndex + stageRubbishDetailList.size() - 1, 3,
                    3));
          }
          startRowIndex = startRowIndex + stageRubbishDetailList.size();
        }
      }
    }

    rowIndex = 1;
    stageRowStartIndex = 21 + baseList.size() + mainList.size() + rowIndex;
    List<StageRubbishDetail> renovationList = stageRubbishDetailsGroup.get("renovation");
    if (renovationList != null && renovationList.size() > 0) {
      Map<String, List<StageRubbishDetail>> renovationGroup = getRubbishDetailGroup(renovationList);
      for (Map.Entry<String, List<StageRubbishDetail>> entry : renovationGroup.entrySet()) {
        List<StageRubbishDetail> groupList = entry.getValue();
        for (StageRubbishDetail stageRubbishDetail : groupList) {
          row = sheet.createRow(21 + baseList.size() + mainList.size() + rowIndex);
          row.setHeightInPoints(30f);
          cell = row.createCell(0);
          cell.setCellStyle(cellStyle);
          cell.setCellValue("");

          cell = row.createCell(1);
          cell.setCellStyle(labelCellStyle);
          cell.setCellValue("装修阶段");

          cell = row.createCell(2);
          cell.setCellStyle(cellStyle);
          cell.setCellValue(
              convertDate(rubbishExpand.getDecorateStageStartDate()) + "-" + convertDate(
                  rubbishExpand.getDecorateStageEndDate()));

          cell = row.createCell(3);
          cell.setCellStyle(labelCellStyle);
          cell.setCellValue(stageRubbishDetail.getRubbishType());

          cell = row.createCell(4);
          cell.setCellStyle(labelCellStyle);
          cell.setCellValue(stageRubbishDetail.getRubbishName());

          cell = row.createCell(5);
          cell.setCellStyle(cellStyle);
          cell.setCellValue(stageRubbishDetail.getResource());

          cell = row.createCell(6);
          cell.setCellStyle(cellStyle);
          cell.setCellValue(stageRubbishDetail.getAddress());

          cell = row.createCell(7);
          cell.setCellStyle(cellStyle);
          cell.setCellValue(stageRubbishDetail.getNum());

          cell = row.createCell(8);
          cell.setCellStyle(cellStyle);
          cell.setCellValue(stageRubbishDetail.getProportion());

          rowIndex = rowIndex + 1;
        }
      }

      if (renovationList.size() > 1) {
        sheet.addMergedRegion(
            new CellRangeAddress(stageRowStartIndex, stageRowStartIndex + renovationList.size() - 1,
                1, 1));
        sheet.addMergedRegion(
            new CellRangeAddress(stageRowStartIndex, stageRowStartIndex + renovationList.size() - 1,
                2, 2));
        int startRowIndex = stageRowStartIndex;
        for (Map.Entry<String, List<StageRubbishDetail>> entry : renovationGroup.entrySet()) {
          List<StageRubbishDetail> stageRubbishDetailList = entry.getValue();
          if (stageRubbishDetailList.size() > 1) {
            sheet.addMergedRegion(
                new CellRangeAddress(startRowIndex,
                    startRowIndex + stageRubbishDetailList.size() - 1, 3,
                    3));
          }
          startRowIndex = startRowIndex + stageRubbishDetailList.size();
        }
      }
    }

    rowIndex = 1;
    stageRowStartIndex = 21 + baseList.size() + mainList.size() + renovationList.size() + rowIndex;
    List<StageRubbishDetail> deliverList = stageRubbishDetailsGroup.get("deliver");
    if (deliverList != null && deliverList.size() > 0) {
      Map<String, List<StageRubbishDetail>> deliverGroup = getRubbishDetailGroup(deliverList);
      for (Map.Entry<String, List<StageRubbishDetail>> entry : deliverGroup.entrySet()) {
        List<StageRubbishDetail> groupList = entry.getValue();
        for (StageRubbishDetail stageRubbishDetail : groupList) {
          row = sheet.createRow(
              21 + baseList.size() + mainList.size() + renovationList.size() + rowIndex);
          row.setHeightInPoints(30f);
          cell = row.createCell(0);
          cell.setCellStyle(cellStyle);
          cell.setCellValue("");

          cell = row.createCell(1);
          cell.setCellStyle(labelCellStyle);
          cell.setCellValue("交付整修阶段");

          cell = row.createCell(2);
          cell.setCellStyle(cellStyle);
          cell.setCellValue(
              convertDate(rubbishExpand.getSubmitStageStartDate()) + "-" + convertDate(
                  rubbishExpand.getSubmitStageEndDate()));

          cell = row.createCell(3);
          cell.setCellStyle(labelCellStyle);
          cell.setCellValue(stageRubbishDetail.getRubbishType());

          cell = row.createCell(4);
          cell.setCellStyle(labelCellStyle);
          cell.setCellValue(stageRubbishDetail.getRubbishName());

          cell = row.createCell(5);
          cell.setCellStyle(cellStyle);
          cell.setCellValue(stageRubbishDetail.getResource());

          cell = row.createCell(6);
          cell.setCellStyle(cellStyle);
          cell.setCellValue(stageRubbishDetail.getAddress());

          cell = row.createCell(7);
          cell.setCellStyle(cellStyle);
          cell.setCellValue(stageRubbishDetail.getNum());

          cell = row.createCell(8);
          cell.setCellStyle(cellStyle);
          cell.setCellValue(stageRubbishDetail.getProportion());

          rowIndex = rowIndex + 1;
        }
      }

      if (deliverList.size() > 1) {
        sheet.addMergedRegion(
            new CellRangeAddress(stageRowStartIndex, stageRowStartIndex + deliverList.size() - 1, 1,
                1));
        sheet.addMergedRegion(
            new CellRangeAddress(stageRowStartIndex, stageRowStartIndex + deliverList.size() - 1, 2,
                2));
        int startRowIndex = stageRowStartIndex;
        for (Map.Entry<String, List<StageRubbishDetail>> entry : deliverGroup.entrySet()) {
          List<StageRubbishDetail> stageRubbishDetailList = entry.getValue();
          if (stageRubbishDetailList.size() > 1) {
            sheet.addMergedRegion(
                new CellRangeAddress(startRowIndex,
                    startRowIndex + stageRubbishDetailList.size() - 1, 3,
                    3));
          }
          startRowIndex = startRowIndex + stageRubbishDetailList.size();
        }
      }
    }

    boolean hasStage = true;
    if (baseList.size() == 0 && mainList.size() == 0 && renovationList.size() == 0
        && deliverList.size() == 0) {
      hasStage = false;
    }
    if (hasStage) {
      sheet.addMergedRegion(new CellRangeAddress(21, 21 + baseList.size() + mainList.size() +
          renovationList.size() + deliverList.size(), 0, 0));
    } else {
      row = sheet.createRow(22);
      cell = row.createCell(0);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");

      cell = row.createCell(1);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");

      cell = row.createCell(2);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");

      cell = row.createCell(3);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");

      cell = row.createCell(4);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");

      cell = row.createCell(5);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");

      cell = row.createCell(6);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");

      cell = row.createCell(7);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");

      cell = row.createCell(8);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");

      sheet.addMergedRegion(new CellRangeAddress(22, 22, 3, 4));
    }

    if (hasStage) {
      stageRowStartIndex =
          21 + baseList.size() + mainList.size() + renovationList.size() + deliverList.size() + 1;
    } else {
      stageRowStartIndex = 23;
    }

    row = sheet.createRow(stageRowStartIndex);
    row.setHeightInPoints(30f);
    cell = row.createCell(0);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("上述所有垃圾总量及其占比统计");

    for (int i = 1; i <= 6; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }

    cell = row.createCell(7);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(convertBigDecimal(rubbishExpand.getRubbishTotalWeight()));

    cell = row.createCell(8);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(convertBigDecimal(rubbishExpand.getRubbishTotalRatio()));

    sheet.addMergedRegion(new CellRangeAddress(stageRowStartIndex, stageRowStartIndex, 0, 6));

    stageRowStartIndex = stageRowStartIndex + 1;
    row = sheet.createRow(stageRowStartIndex);
    row.setHeightInPoints(40f);
    cell = row.createCell(0);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("相关要求");

    for (int i = 1; i <= 2; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }

    cell = row.createCell(3);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("1.本表需要建设单位、施工单位、资源化企业、运输企业盖章；2.资源化利用合同、运输合同与本表一起报送。");

    for (int i = 4; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }

    sheet.addMergedRegion(new CellRangeAddress(stageRowStartIndex, stageRowStartIndex, 0, 2));
    sheet.addMergedRegion(new CellRangeAddress(stageRowStartIndex, stageRowStartIndex, 3, 8));

    return wb;
  }

  /**
   * 拆迁工程建筑垃圾资源化利用信息备案表
   *
   * @param project                  Project
   * @param rubbishRemoval           ProjectRubbishRemoval
   * @param idField                  Map<String,String>
   * @param stageRubbishDetailsGroup List<StageRubbishDetail>>
   * @return SXSSFWorkbook
   */
  public static SXSSFWorkbook createRecordTable(Project project,
      ProjectRubbishRemoval rubbishRemoval,
      Map<String, String> idField, Map<String, List<StageRubbishDetail>> stageRubbishDetailsGroup) {

    SXSSFWorkbook wb = new SXSSFWorkbook();
    Sheet sheet = wb.createSheet("拆迁工程建筑垃圾资源化利用信息备案表");
    sheet.setColumnWidth(1, 5000);
    sheet.setColumnWidth(2, 4000);
    sheet.setColumnWidth(3, 4000);
    sheet.setColumnWidth(4, 4000);
    sheet.setColumnWidth(5, 5000);
    sheet.setColumnWidth(6, 4000);
    sheet.setColumnWidth(7, 3000);
    sheet.setColumnWidth(8, 3000);

    CellStyle titleCellStyle = wb.createCellStyle();
    Font titleCellFont = wb.createFont();
    titleCellFont.setBold(true);
    titleCellFont.setFontName("微软雅黑");
    titleCellFont.setFontHeightInPoints((short) 14);
    titleCellStyle.setFont(titleCellFont);
    titleCellStyle.setAlignment(HorizontalAlignment.CENTER);
    titleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

    CellStyle labelCellStyle = wb.createCellStyle();
    Font labelCellFont = wb.createFont();
    labelCellFont.setBold(true);
    labelCellFont.setFontName("微软雅黑");
    labelCellFont.setFontHeightInPoints((short) 11);
    labelCellStyle.setFont(labelCellFont);
    labelCellStyle.setBorderBottom(BorderStyle.THIN);
    labelCellStyle.setBorderLeft(BorderStyle.THIN);
    labelCellStyle.setBorderTop(BorderStyle.THIN);
    labelCellStyle.setBorderRight(BorderStyle.THIN);
    labelCellStyle.setWrapText(true);
    labelCellStyle.setAlignment(HorizontalAlignment.CENTER);
    labelCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

    CellStyle cellStyle = wb.createCellStyle();
    Font cellFont = wb.createFont();
    cellFont.setFontName("微软雅黑");
    cellFont.setFontHeightInPoints((short) 11);
    cellStyle.setFont(cellFont);
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setWrapText(true);
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

    Row row = sheet.createRow(0);
    row.setHeightInPoints(40f);
    Cell cell = row.createCell(0);
    cell.setCellStyle(titleCellStyle);
    cell.setCellValue("拆迁工程建筑垃圾资源化利用信息备案表");

    for (int i = 1; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));

    row = sheet.createRow(1);
    row.setHeightInPoints(30f);
    cell = row.createCell(0);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("项目基本信息");

    cell = row.createCell(1);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("项目名称");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(project.getName());
    for (int i = 3; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }

    sheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 8));

    row = sheet.createRow(2);
    row.setHeightInPoints(30f);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("项目地址");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(project.getAddress());
    for (int i = 3; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 2, 8));

    row = sheet.createRow(3);
    row.setHeightInPoints(30f);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("建设内容");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(rubbishRemoval.getBuildingYear() + "-" + idField.get("structureType"));
    for (int i = 3; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(3, 3, 2, 8));

    row = sheet.createRow(4);
    row.setHeightInPoints(30f);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("拆除工期");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(rubbishRemoval.getRemovalPeriod());
    for (int i = 3; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(4, 4, 2, 8));

    row = sheet.createRow(5);
    row.setHeightInPoints(30f);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("曾经用途");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(rubbishRemoval.getBuildingUse());
    for (int i = 3; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(5, 5, 2, 8));

    row = sheet.createRow(6);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("征收实施单位");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("单位名称");

    cell = row.createCell(3);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(rubbishRemoval.getConstructionDept());

    for (int i = 4; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(6, 6, 3, 8));

    row = sheet.createRow(7);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("联系人及电话");

    cell = row.createCell(3);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(
        rubbishRemoval.getConstructionPerson() + "-" + rubbishRemoval.getConstructionPhone());

    for (int i = 4; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(7, 7, 3, 8));

    sheet.addMergedRegion(new CellRangeAddress(6, 7, 1, 1));

    row = sheet.createRow(8);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("拆除单位");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("单位名称");

    cell = row.createCell(3);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(rubbishRemoval.getRemovalDept());

    for (int i = 4; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(8, 8, 3, 8));

    row = sheet.createRow(9);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("联系人及电话");

    cell = row.createCell(3);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(rubbishRemoval.getRemovalPerson() + "-" + rubbishRemoval.getRemovalPhone());

    for (int i = 4; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(9, 9, 3, 8));

    sheet.addMergedRegion(new CellRangeAddress(8, 9, 1, 1));

    row = sheet.createRow(10);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("资源化利用处置单位");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("单位名称");

    cell = row.createCell(3);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(idField.get("disposalLand"));

    for (int i = 4; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(10, 10, 3, 8));

    row = sheet.createRow(11);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("联系人及电话");

    cell = row.createCell(3);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(
        rubbishRemoval.getDisposalLandPerson() + "-" + rubbishRemoval.getDisposalLandPhone());

    for (int i = 4; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(11, 11, 3, 8));

    sheet.addMergedRegion(new CellRangeAddress(10, 11, 1, 1));

    row = sheet.createRow(12);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("运输单位");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("单位名称");

    cell = row.createCell(3);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(idField.get("transportCompany"));

    for (int i = 4; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(12, 12, 3, 8));

    row = sheet.createRow(13);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("联系人及电话");

    cell = row.createCell(3);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(rubbishRemoval.getTransportCompanyPerson() + "-"
        + rubbishRemoval.getTransportCompanyPhone());

    for (int i = 4; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(13, 13, 3, 8));

    sheet.addMergedRegion(new CellRangeAddress(12, 13, 1, 1));

    row = sheet.createRow(14);
    row.setHeightInPoints(30f);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("建筑体量");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(
        "总共拆除面积约" + convertBigDecimal(rubbishRemoval.getRubbishRemovalArea()) + "m²、其中住宅面积约"
            + convertBigDecimal(rubbishRemoval.getRubbishHouseArea()) +
            "m²，预计产生建筑垃圾约" + convertBigDecimal(rubbishRemoval.getRubbishPlanWeight()) + "吨，其中可利用的约"
            + convertBigDecimal(rubbishRemoval.getRubbishRecycleWeight()) +
            "吨，利用率预计可以达到" + convertBigDecimal(rubbishRemoval.getRubbishRecycleRatio()) + "%");
    for (int i = 3; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(14, 14, 2, 8));

    row = sheet.createRow(15);
    row.setHeightInPoints(30f);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("预计垃圾清运时间");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(convertDate(rubbishRemoval.getRubbishPlanTransportDate()));
    for (int i = 3; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(15, 15, 2, 8));

    row = sheet.createRow(16);
    row.setHeightInPoints(40f);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("运输车辆信息");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(idField.get("transportCompanyCarInfo"));

    for (int i = 3; i <= 8; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(16, 16, 2, 8));

    sheet.addMergedRegion(new CellRangeAddress(1, 16, 0, 0));

    row = sheet.createRow(17);
    row.setHeightInPoints(30f);
    cell = row.createCell(0);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("资源化利用情况");

    cell = row.createCell(1);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("实施阶段");

    cell = row.createCell(2);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("清运时间");

    cell = row.createCell(3);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("垃圾种类");

    cell = row.createCell(4);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(5);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("资源化利用再生材料");

    cell = row.createCell(6);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("去向或使用场合");

    cell = row.createCell(7);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("数量（t）");

    cell = row.createCell(8);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("占比（%）");

    sheet.addMergedRegion(new CellRangeAddress(17, 17, 3, 4));

    int rowIndex = 1;
    int stageRowStartIndex = 17 + rowIndex;
    List<StageRubbishDetail> dismantleList = stageRubbishDetailsGroup.get("dismantle");
    if (dismantleList != null && dismantleList.size() > 0) {
      Map<String, List<StageRubbishDetail>> dismantleGroup = getRubbishDetailGroup(dismantleList);
      for (Map.Entry<String, List<StageRubbishDetail>> entry : dismantleGroup.entrySet()) {
        List<StageRubbishDetail> groupList = entry.getValue();
        for (StageRubbishDetail stageRubbishDetail : groupList) {
          row = sheet.createRow(17 + rowIndex);
          cell = row.createCell(0);
          cell.setCellStyle(cellStyle);
          cell.setCellValue("");

          cell = row.createCell(1);
          cell.setCellStyle(labelCellStyle);
          cell.setCellValue("垃圾分拣拆除计划");

          cell = row.createCell(2);
          cell.setCellStyle(cellStyle);
          cell.setCellValue(
              convertDate(rubbishRemoval.getClassifyStageStartDate()) + "-" + convertDate(
                  rubbishRemoval.getClassifyStageEndDate()));

          cell = row.createCell(3);
          cell.setCellStyle(labelCellStyle);
          cell.setCellValue(stageRubbishDetail.getRubbishType());

          cell = row.createCell(4);
          cell.setCellStyle(labelCellStyle);
          cell.setCellValue(stageRubbishDetail.getRubbishName());

          cell = row.createCell(5);
          cell.setCellStyle(cellStyle);
          cell.setCellValue(stageRubbishDetail.getResource());

          cell = row.createCell(6);
          cell.setCellStyle(cellStyle);
          cell.setCellValue(stageRubbishDetail.getAddress());

          cell = row.createCell(7);
          cell.setCellStyle(cellStyle);
          cell.setCellValue(stageRubbishDetail.getNum());

          cell = row.createCell(8);
          cell.setCellStyle(cellStyle);
          cell.setCellValue(stageRubbishDetail.getProportion());

          rowIndex = rowIndex + 1;
        }
      }

      if (dismantleList.size() > 1) {
        sheet.addMergedRegion(
            new CellRangeAddress(stageRowStartIndex, stageRowStartIndex + dismantleList.size() - 1,
                1, 1));
        sheet.addMergedRegion(
            new CellRangeAddress(stageRowStartIndex, stageRowStartIndex + dismantleList.size() - 1,
                2, 2));
        int startRowIndex = stageRowStartIndex;
        for (Map.Entry<String, List<StageRubbishDetail>> entry : dismantleGroup.entrySet()) {
          List<StageRubbishDetail> stageRubbishDetailList = entry.getValue();
          if (stageRubbishDetailList.size() > 1) {
            sheet.addMergedRegion(
                new CellRangeAddress(startRowIndex,
                    startRowIndex + stageRubbishDetailList.size() - 1, 3,
                    3));
          }
          startRowIndex = startRowIndex + stageRubbishDetailList.size();
        }
      }
    }

    rowIndex = 1;
    stageRowStartIndex = 17 + dismantleList.size() + rowIndex;
    List<StageRubbishDetail> useList = stageRubbishDetailsGroup.get("use");
    Map<String, List<StageRubbishDetail>> useGroup = getRubbishDetailGroup(useList);
    for (Map.Entry<String, List<StageRubbishDetail>> entry : useGroup.entrySet()) {
      List<StageRubbishDetail> groupList = entry.getValue();
      for (StageRubbishDetail stageRubbishDetail : groupList) {
        row = sheet.createRow(17 + dismantleList.size() + rowIndex);
        cell = row.createCell(0);
        cell.setCellStyle(cellStyle);
        cell.setCellValue("");

        cell = row.createCell(1);
        cell.setCellStyle(labelCellStyle);
        cell.setCellValue("建筑垃圾资源化利用计划");

        cell = row.createCell(2);
        cell.setCellStyle(cellStyle);
        cell.setCellValue(
            convertDate(rubbishRemoval.getRecycleStageStartDate()) + "-" + convertDate(
                rubbishRemoval.getRecycleStageEndDate()));

        cell = row.createCell(3);
        cell.setCellStyle(labelCellStyle);
        cell.setCellValue(stageRubbishDetail.getRubbishType());

        cell = row.createCell(4);
        cell.setCellStyle(labelCellStyle);
        cell.setCellValue(stageRubbishDetail.getRubbishName());

        cell = row.createCell(5);
        cell.setCellStyle(cellStyle);
        cell.setCellValue(stageRubbishDetail.getResource());

        cell = row.createCell(6);
        cell.setCellStyle(cellStyle);
        cell.setCellValue(stageRubbishDetail.getAddress());

        cell = row.createCell(7);
        cell.setCellStyle(cellStyle);
        cell.setCellValue(stageRubbishDetail.getNum());

        cell = row.createCell(8);
        cell.setCellStyle(cellStyle);
        cell.setCellValue(stageRubbishDetail.getProportion());

        rowIndex = rowIndex + 1;
      }
    }

    if (useList.size() > 1) {
      sheet.addMergedRegion(
          new CellRangeAddress(stageRowStartIndex, stageRowStartIndex + useList.size() - 1, 1, 1));
      sheet.addMergedRegion(
          new CellRangeAddress(stageRowStartIndex, stageRowStartIndex + useList.size() - 1, 2, 2));
      int startRowIndex = stageRowStartIndex;
      for (Map.Entry<String, List<StageRubbishDetail>> entry : useGroup.entrySet()) {
        List<StageRubbishDetail> stageRubbishDetailList = entry.getValue();
        if (stageRubbishDetailList.size() > 1) {
          sheet.addMergedRegion(
              new CellRangeAddress(startRowIndex, startRowIndex + stageRubbishDetailList.size() - 1,
                  3,
                  3));
        }
        startRowIndex = startRowIndex + stageRubbishDetailList.size();
      }
    }

    boolean hasStage = true;
    if (dismantleList.size() == 0 && useList.size() == 0) {
      hasStage = false;
    }
    if (hasStage) {
      sheet.addMergedRegion(
          new CellRangeAddress(17, 17 + dismantleList.size() + useList.size(), 0, 0));
    } else {
      row = sheet.createRow(18);
      cell = row.createCell(0);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");

      cell = row.createCell(1);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");

      cell = row.createCell(2);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");

      cell = row.createCell(3);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");

      cell = row.createCell(4);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");

      cell = row.createCell(5);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");

      cell = row.createCell(6);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");

      cell = row.createCell(7);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");

      cell = row.createCell(8);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");

      sheet.addMergedRegion(new CellRangeAddress(18, 18, 3, 4));
    }
    return wb;
  }

  /**
   * 建筑垃圾运输处置申报表
   *
   * @param project       Project
   * @param rubbishReport ProjectRubbishReport
   * @param idField       Map<String,String>
   * @return SXSSFWorkbook
   */
  public static SXSSFWorkbook createApplyTable(Project project, ProjectRubbishReport rubbishReport,
      Map<String, String> idField) {

    SXSSFWorkbook wb = new SXSSFWorkbook();
    Sheet sheet = wb.createSheet("建筑垃圾运输处置方案申报表");
    sheet.setColumnWidth(0, 4000);
    sheet.setColumnWidth(1, 4000);
    sheet.setColumnWidth(2, 4000);
    sheet.setColumnWidth(3, 4000);
    sheet.setColumnWidth(4, 7000);

    CellStyle titleCellStyle = wb.createCellStyle();
    Font titleCellFont = wb.createFont();
    titleCellFont.setBold(true);
    titleCellFont.setFontName("微软雅黑");
    titleCellFont.setFontHeightInPoints((short) 14);
    titleCellStyle.setFont(titleCellFont);
    titleCellStyle.setAlignment(HorizontalAlignment.CENTER);
    titleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

    CellStyle labelCellStyle = wb.createCellStyle();
    Font labelCellFont = wb.createFont();
    labelCellFont.setBold(true);
    labelCellFont.setFontName("微软雅黑");
    labelCellFont.setFontHeightInPoints((short) 11);
    labelCellStyle.setFont(labelCellFont);
    labelCellStyle.setBorderBottom(BorderStyle.THIN);
    labelCellStyle.setBorderLeft(BorderStyle.THIN);
    labelCellStyle.setBorderTop(BorderStyle.THIN);
    labelCellStyle.setBorderRight(BorderStyle.THIN);
    labelCellStyle.setWrapText(true);
    labelCellStyle.setAlignment(HorizontalAlignment.CENTER);
    labelCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

    CellStyle cellStyle = wb.createCellStyle();
    Font cellFont = wb.createFont();
    cellFont.setFontName("微软雅黑");
    cellFont.setFontHeightInPoints((short) 11);
    cellStyle.setFont(cellFont);
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setWrapText(true);
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

    Row row = sheet.createRow(0);
    row.setHeightInPoints(40f);
    Cell cell = row.createCell(0);
    cell.setCellStyle(titleCellStyle);
    cell.setCellValue("建筑垃圾运输处置方案申报表");

    for (int i = 1; i <= 4; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }

    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));

    row = sheet.createRow(1);
    row.setHeightInPoints(30f);
    cell = row.createCell(0);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("项目信息");

    cell = row.createCell(1);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("项目名称");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(project.getName());
    for (int i = 3; i <= 4; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }

    sheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 4));

    row = sheet.createRow(2);
    row.setHeightInPoints(30f);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("项目地址");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(project.getAddress());
    for (int i = 3; i <= 4; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(2, 2, 2, 4));

    row = sheet.createRow(3);
    row.setHeightInPoints(30f);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("建筑垃圾种类");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(idField.get("rubbishType"));
    for (int i = 3; i <= 4; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(3, 3, 2, 4));

    row = sheet.createRow(4);
    row.setHeightInPoints(30f);
    cell = row.createCell(0);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(1);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("处置数量");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(
        "建筑垃圾约" + convertBigDecimal(rubbishReport.getDisposalArea()) + "m²,约" + convertBigDecimal(
            rubbishReport.getDisposalWeight()) + "吨");
    for (int i = 3; i <= 4; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }
    sheet.addMergedRegion(new CellRangeAddress(4, 4, 2, 4));
    sheet.addMergedRegion(new CellRangeAddress(1, 4, 0, 0));

    row = sheet.createRow(5);
    row.setHeightInPoints(30f);
    cell = row.createCell(0);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("施工单位");

    cell = row.createCell(1);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(rubbishReport.getConstructionDept());

    cell = row.createCell(3);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("项目经理");

    cell = row.createCell(4);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(rubbishReport.getConstructionPerson());

    row = sheet.createRow(6);
    row.setHeightInPoints(30f);
    cell = row.createCell(0);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("施工单位");

    cell = row.createCell(1);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(rubbishReport.getConstructionDept());

    cell = row.createCell(3);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("联系电话");

    cell = row.createCell(4);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(rubbishReport.getConstructionPhone());

    sheet.addMergedRegion(new CellRangeAddress(5, 6, 0, 1));
    sheet.addMergedRegion(new CellRangeAddress(5, 6, 2, 2));

    row = sheet.createRow(7);
    row.setHeightInPoints(30f);
    cell = row.createCell(0);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("监理单位");

    cell = row.createCell(1);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(rubbishReport.getSupervisorDept());

    cell = row.createCell(3);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("项目负责人");

    cell = row.createCell(4);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(rubbishReport.getSupervisorPerson());

    row = sheet.createRow(8);
    row.setHeightInPoints(30f);
    cell = row.createCell(0);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("监理单位");

    cell = row.createCell(1);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(rubbishReport.getSupervisorDept());

    cell = row.createCell(3);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("联系电话");

    cell = row.createCell(4);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(rubbishReport.getSupervisorPhone());

    sheet.addMergedRegion(new CellRangeAddress(7, 8, 0, 1));
    sheet.addMergedRegion(new CellRangeAddress(7, 8, 2, 2));

    row = sheet.createRow(9);
    row.setHeightInPoints(30f);
    cell = row.createCell(0);
    cell.setCellStyle(titleCellStyle);
    cell.setCellValue("申报内容");

    for (int i = 1; i <= 4; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }

    sheet.addMergedRegion(new CellRangeAddress(9, 9, 0, 4));

    row = sheet.createRow(10);
    row.setHeightInPoints(30f);
    cell = row.createCell(0);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("项目类型");

    cell = row.createCell(1);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(idField.get("projectKind"));

    for (int i = 3; i <= 4; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }

    sheet.addMergedRegion(new CellRangeAddress(10, 10, 0, 1));
    sheet.addMergedRegion(new CellRangeAddress(10, 10, 2, 4));

    row = sheet.createRow(11);
    row.setHeightInPoints(30f);
    cell = row.createCell(0);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("垃圾预估量");

    cell = row.createCell(1);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(convertBigDecimal(rubbishReport.getDisposalPlanWeight()) + "吨");

    for (int i = 3; i <= 4; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }

    sheet.addMergedRegion(new CellRangeAddress(11, 11, 0, 1));
    sheet.addMergedRegion(new CellRangeAddress(11, 11, 2, 4));

    row = sheet.createRow(12);
    row.setHeightInPoints(30f);
    cell = row.createCell(0);
    cell.setCellStyle(labelCellStyle);
    cell.setCellValue("监理单位意见");

    cell = row.createCell(1);
    cell.setCellStyle(cellStyle);
    cell.setCellValue("");

    cell = row.createCell(2);
    cell.setCellStyle(cellStyle);
    cell.setCellValue(rubbishReport.getSupervisorSuggestion());

    for (int i = 3; i <= 4; i++) {
      cell = row.createCell(i);
      cell.setCellStyle(cellStyle);
      cell.setCellValue("");
    }

    sheet.addMergedRegion(new CellRangeAddress(12, 12, 0, 1));
    sheet.addMergedRegion(new CellRangeAddress(12, 12, 2, 4));

    return wb;
  }

  /**
   * 生成电子档(预约清理)
   */
  public static Workbook createDocBooking(Project project, BookingDTO bookingDTO,
      Map<String, String> idField) {
    Workbook wb = new SXSSFWorkbook();
    Sheet sheet = wb.createSheet("固废清运申报表");
    sheet.setColumnWidth(0, 4000);
    sheet.setColumnWidth(1, 4000);
    sheet.setColumnWidth(2, 4000);
    sheet.setColumnWidth(3, 4000);
    sheet.setColumnWidth(4, 7000);
    // 样式
    CellStyle titleCellStyle = createTitleCellStyle(wb);
    CellStyle labelCellStyle = createLabelCellStyle(wb);
    CellStyle cellStyle = createValueCellStyle(wb);
    // 标题
    int rowNum = 0;
    Row row = createRow(sheet, rowNum, TITLE_HEIGHT);
    setCellValue(row, 0, titleCellStyle, "建筑垃圾清运申报表");
    sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, 4));
    rowNum++;

    row = createRow(sheet, rowNum, ROW_HEIGHT);
    setCellValue(row, 0, labelCellStyle, "项目信息");
    setCellValue(row, 1, labelCellStyle, "项目名称");
    setCellValue(row, 2, cellStyle, project.getName());
    setCellValueEmpty(row, cellStyle, 3, 4);
    sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 2, 4));
    rowNum++;

    row = createRow(sheet, rowNum, ROW_HEIGHT);
    setCellValue(row, 1, labelCellStyle, "项目地址");
    setCellValue(row, 2, cellStyle, project.getAddress());
    setCellValueEmpty(row, cellStyle, 3, 4);
    sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 2, 4));
    rowNum++;

    row = createRow(sheet, rowNum, ROW_HEIGHT);
    setCellValue(row, 1, labelCellStyle, "建筑垃圾种类");
    setCellValue(row, 2, cellStyle, idField.get("rubbishType"));
    setCellValueEmpty(row, cellStyle, 3, 4);
    sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 2, 4));
    rowNum++;

    row = createRow(sheet, rowNum, ROW_HEIGHT);
    setCellValue(row, 1, labelCellStyle, "处置数量");
    setCellValue(row, 2, cellStyle,
        "建筑垃圾约" + convertBigDecimal(bookingDTO.getDisposalArea()) + "m²,约" + convertBigDecimal(
            bookingDTO.getDisposalWeight()) + "吨");
    setCellValueEmpty(row, cellStyle, 3, 4);
    sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 2, 4));
    rowNum++;

    row = createRow(sheet, rowNum, ROW_HEIGHT);
    setCellValue(row, 1, labelCellStyle, "预约时间");
    setCellValue(row, 2, cellStyle, DateUtil.formatDate(bookingDTO.getBookingTime()));
    setCellValueEmpty(row, cellStyle, 3, 4);
    sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 2, 4));
    rowNum++;

    row = createRow(sheet, rowNum, ROW_HEIGHT);
    setCellValue(row, 1, labelCellStyle, "车辆类型");
    setCellValue(row, 2, cellStyle, idField.get("carTypeClassId"));
    setCellValueEmpty(row, cellStyle, 3, 4);
    sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 2, 4));
    rowNum++;

    row = createRow(sheet, rowNum, ROW_HEIGHT);
    setCellValue(row, 1, labelCellStyle, "预计车数");
    setCellValue(row, 2, cellStyle, String.valueOf(bookingDTO.getBookingCarNumber()));
    setCellValueEmpty(row, cellStyle, 3, 4);
    sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 2, 4));
    rowNum++;
    // 合并项目信息
    sheet.addMergedRegion(new CellRangeAddress(1, rowNum - 1, 0, 0));

    row = createRow(sheet, rowNum, ROW_HEIGHT);
    setCellValue(row, 0, labelCellStyle, "施工单位");
    setCellValue(row, 2, cellStyle, bookingDTO.getConstructionDept());
    setCellValue(row, 3, labelCellStyle, "项目经理");
    setCellValue(row, 4, cellStyle, bookingDTO.getConstructionPerson());
    rowNum++;
    row = createRow(sheet, rowNum, ROW_HEIGHT);
    setCellValueEmpty(row, cellStyle, 0, 1, 2);
    setCellValue(row, 3, labelCellStyle, "联系电话");
    setCellValue(row, 4, cellStyle, bookingDTO.getConstructionPhone());
    sheet.addMergedRegion(new CellRangeAddress(rowNum - 1, rowNum, 0, 1));
    sheet.addMergedRegion(new CellRangeAddress(rowNum - 1, rowNum, 2, 2));
    rowNum++;

    row = createRow(sheet, rowNum, ROW_HEIGHT);
    setCellValue(row, 0, labelCellStyle, "监理单位");
    setCellValue(row, 2, cellStyle, bookingDTO.getSupervisorDept());
    setCellValue(row, 3, labelCellStyle, "项目负责人");
    setCellValue(row, 4, cellStyle, bookingDTO.getSupervisorPerson());
    rowNum++;
    row = createRow(sheet, rowNum, ROW_HEIGHT);
    setCellValueEmpty(row, cellStyle, 0, 1, 2);
    setCellValue(row, 3, labelCellStyle, "联系电话");
    setCellValue(row, 4, cellStyle, bookingDTO.getSupervisorPhone());
    sheet.addMergedRegion(new CellRangeAddress(rowNum - 1, rowNum, 0, 1));
    sheet.addMergedRegion(new CellRangeAddress(rowNum - 1, rowNum, 2, 2));
    rowNum++;

    row = createRow(sheet, rowNum, TITLE_HEIGHT);
    setCellValue(row, 0, labelCellStyle, "申报内容");
    setCellValueEmpty(row, cellStyle, 1, 2, 3, 4);
    sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, 4));
    rowNum++;

    row = createRow(sheet, rowNum, ROW_HEIGHT);
    setCellValue(row, 0, labelCellStyle, "项目类型");
    setCellValue(row, 2, cellStyle, idField.get("projectKind"));
    setCellValueEmpty(row, cellStyle, 1, 3, 4);
    sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, 1));
    sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 2, 4));
    rowNum++;

    row = createRow(sheet, rowNum, ROW_HEIGHT);
    setCellValue(row, 0, labelCellStyle, "项目进度");
    setCellValue(row, 2, cellStyle, idField.get("rubbishStage"));
    setCellValueEmpty(row, cellStyle, 1, 3, 4);
    sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, 1));
    sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 2, 4));
    rowNum++;

    row = createRow(sheet, rowNum, ROW_HEIGHT);
    setCellValue(row, 0, labelCellStyle, "垃圾预估量");
    setCellValue(row, 2, cellStyle, String.valueOf(bookingDTO.getDisposalPlanWeight()) + "吨");
    setCellValueEmpty(row, cellStyle, 1, 3, 4);
    sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, 1));
    sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 2, 4));
    rowNum++;

    row = createRow(sheet, rowNum, ROW_HEIGHT);
    setCellValue(row, 0, labelCellStyle, "无机类数量(吨)");
    setCellValue(row, 2, cellStyle, String.valueOf(bookingDTO.getInorganicWeight()) + "吨");
    setCellValueEmpty(row, cellStyle, 1, 3, 4);
    sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, 1));
    sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 2, 4));
    rowNum++;

    row = createRow(sheet, rowNum, ROW_HEIGHT);
    setCellValue(row, 0, labelCellStyle, "废金属数量(吨)");
    setCellValue(row, 2, cellStyle, String.valueOf(bookingDTO.getMetalWeight()) + "吨");
    setCellValueEmpty(row, cellStyle, 1, 3, 4);
    sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, 1));
    sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 2, 4));
    rowNum++;

    row = createRow(sheet, rowNum, ROW_HEIGHT);
    setCellValue(row, 0, labelCellStyle, "废木材(吨)");
    setCellValue(row, 2, cellStyle, String.valueOf(bookingDTO.getWoodWeight()) + "吨");
    setCellValueEmpty(row, cellStyle, 1, 3, 4);
    sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, 1));
    sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 2, 4));
    rowNum++;

    row = createRow(sheet, rowNum, ROW_HEIGHT);
    setCellValue(row, 0, labelCellStyle, "其他类(吨)");
    setCellValue(row, 2, cellStyle, String.valueOf(bookingDTO.getOtherWeight()) + "吨");
    setCellValueEmpty(row, cellStyle, 1, 3, 4);
    sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, 1));
    sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 2, 4));
    rowNum++;

    row = createRow(sheet, rowNum, ROW_HEIGHT);
    setCellValue(row, 0, labelCellStyle, "监理单位意见");
    setCellValue(row, 2, cellStyle, bookingDTO.getSupervisorSuggestion());
    setCellValueEmpty(row, cellStyle, 1, 3, 4);
    sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 0, 1));
    sheet.addMergedRegion(new CellRangeAddress(rowNum, rowNum, 2, 4));
    rowNum++;

    return wb;
  }

  /**
   * 生成row
   *
   * @param sheet
   * @param rowNum
   * @param height
   * @return
   */
  private static Row createRow(Sheet sheet, int rowNum, float height) {
    Row row = sheet.createRow(rowNum);
    row.setHeightInPoints(height);
    return row;
  }

  /**
   * 生成cell
   *
   * @param row
   * @param style
   * @param value
   * @return
   */
  private static void setCellValue(Row row, int column, CellStyle style, String value) {
    Cell cell = row.createCell(column);
    cell.setCellStyle(style);
    cell.setCellValue(value);
  }

  /**
   * cell设置为空
   */
  private static void setCellValueEmpty(Row row, CellStyle style, Integer... columns) {
    for (Integer column : columns) {
      Cell cell = row.createCell(column);
      cell.setCellStyle(style);
      cell.setCellValue("");
    }

  }

  /**
   * 标题样式
   */
  private static CellStyle createTitleCellStyle(Workbook wb) {
    CellStyle titleCellStyle = wb.createCellStyle();
    Font titleCellFont = wb.createFont();
    titleCellFont.setBold(true);
    titleCellFont.setFontName("微软雅黑");
    titleCellFont.setFontHeightInPoints((short) 14);
    titleCellStyle.setFont(titleCellFont);
    titleCellStyle.setAlignment(HorizontalAlignment.CENTER);
    titleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    return titleCellStyle;
  }

  /**
   * 单元格label
   */
  private static CellStyle createLabelCellStyle(Workbook wb) {
    CellStyle labelCellStyle = wb.createCellStyle();
    Font labelCellFont = wb.createFont();
    labelCellFont.setBold(true);
    labelCellFont.setFontName("微软雅黑");
    labelCellFont.setFontHeightInPoints((short) 11);
    labelCellStyle.setFont(labelCellFont);
    labelCellStyle.setBorderBottom(BorderStyle.THIN);
    labelCellStyle.setBorderLeft(BorderStyle.THIN);
    labelCellStyle.setBorderTop(BorderStyle.THIN);
    labelCellStyle.setBorderRight(BorderStyle.THIN);
    labelCellStyle.setWrapText(true);
    labelCellStyle.setAlignment(HorizontalAlignment.CENTER);
    labelCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    return labelCellStyle;
  }

  /**
   * 单元格value
   */
  private static CellStyle createValueCellStyle(Workbook wb) {
    CellStyle cellStyle = wb.createCellStyle();
    Font cellFont = wb.createFont();
    cellFont.setFontName("微软雅黑");
    cellFont.setFontHeightInPoints((short) 11);
    cellStyle.setFont(cellFont);
    cellStyle.setBorderBottom(BorderStyle.THIN);
    cellStyle.setBorderLeft(BorderStyle.THIN);
    cellStyle.setBorderTop(BorderStyle.THIN);
    cellStyle.setBorderRight(BorderStyle.THIN);
    cellStyle.setWrapText(true);
    cellStyle.setAlignment(HorizontalAlignment.CENTER);
    cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
    return cellStyle;
  }

  /**
   * 按垃圾种类分组
   *
   * @param stageRubbishDetails List<StageRubbishDetail>
   * @return Map<String, List < StageRubbishDetail>>
   */
  private static Map<String, List<StageRubbishDetail>> getRubbishDetailGroup(
      List<StageRubbishDetail> stageRubbishDetails) {
    Map<String, List<StageRubbishDetail>> stageMap =
        stageRubbishDetails.stream().collect(
            Collectors.groupingBy(StageRubbishDetail::getRubbishType, LinkedHashMap::new,
                Collectors.toList()));

    return stageMap;
  }

  /**
   * 日期转换
   *
   * @param date Date
   * @return String
   */
  private static String convertDate(Date date) {
    if (date == null) {
      return "";
    }
    String convertedDate = DateUtils.parseDateToStr(DateUtils.YYYY_MM_DD, date);
    String[] dateInfo = convertedDate.split("-");
    String month = dateInfo[1];
    if (month.startsWith("0")) {
      month = month.replaceFirst("0", "");
    }
    String day = dateInfo[2];
    if (day.startsWith("0")) {
      day = day.replaceFirst("0", "");
    }
    convertedDate = dateInfo[0] + "年" + month + "月" + day + "日";
    return convertedDate;
  }

  /**
   * 处理超大数字
   *
   * @param bigDecimal BigDecimal
   * @return String
   */
  private static String convertBigDecimal(BigDecimal bigDecimal) {
    if (bigDecimal == null) {
      return "";
    }
    return bigDecimal.toPlainString();
  }

  public static void main(String[] args) {
    String str = convertDate(new Date());
    System.out.println(str);

    Calendar calendar = Calendar.getInstance();
    calendar.set(2022, 9, 10);
    str = convertDate(calendar.getTime());
    System.out.println(str);
  }

}
